/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package utopiaintelpackage.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;

import main.Bot;
import main.CommonMethods;
import main.Constants;
import main.Main;

import org.apache.log4j.Logger;

import timers.ArmyTimer;
import utopiaintelpackage.som.SoM;
import botobjects.SoMArmy;
import database.DBConnectionFactory;

/**
 * A class that saves and retreives SoM objects
 * 
 * 
 */
public class SoMtoDB {
   /**
    * If a province doesn't exist yet, it needs be added
    * 
    * @param som
    */
   private static void addProvince(final SoM som) {
	  PreparedStatement ps = null;
	  try {
		 if (!CommonMethods.kdExists(som.getKdloc())) {
			CommonMethods.addDefaultKD(som.getKdloc());
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO province VALUES (?,?,?,?,?,?,?,?,?,false,?)");
		 ps.setString(1, som.getName());
		 ps.setString(2, som.getRace());
		 ps.setString(3, som.getRulerName());
		 ps.setString(4, som.getPersonality());
		 ps.setString(5, som.getTitle());
		 ps.setString(6, som.getKdloc());
		 ps.setInt(7, 0);
		 ps.setInt(8, 0);
		 ps.setInt(9, 0);
		 ps.setTimestamp(10, new Timestamp(som.getUpdated().getTime()));
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   public static SoM getSoM(final String name) {
	  SoM som = null;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("DELETE FROM somarmy WHERE returntime < ?");
		 ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "som,province WHERE som.name = ? AND som.name = province.name");
		 ps.setString(1, name);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			som = new SoM();
			som.setName(rs.getString("name"));
			som.setRace(rs.getString("race"));
			som.setPersonality(rs.getString("personality"));
			som.setTitle(rs.getString("title"));
			som.setKdloc(rs.getString("kdloc"));
			som.addArmy(new SoMArmy(som.getRace(), rs.getInt("soldiers"), rs.getInt("dspecs"), rs.getInt("ospecs"), rs
				  .getInt("elites"), Constants.SOM_ARMY_HOME, 0));
			som.addArmy(new SoMArmy(som.getRace(), 0, rs.getInt("dspecstraining"), rs.getInt("ospecstraining"), rs
				  .getInt("elitestraining"), rs.getInt("thievestraining"), Constants.SOM_ARMY_TRAINING, 0));
			som.setUpdated(new Date(rs.getTimestamp("updated").getTime()));
			som.setExportLine(rs.getString("export"));
			som.setSavedBy(rs.getString("savedby"));
			som.setNetDefense(rs.getInt("netdef"));
			som.setNetOffense(rs.getInt("netoff"));

			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("SELECT * FROM somarmy WHERE name = ?");
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
			   som.addArmy(new SoMArmy(som.getRace(), rs.getTimestamp("returntime").getTime(), rs.getInt("soldiers"), rs
					 .getInt("ospecs"), rs.getInt("elites"), rs.getInt("armyno"), Constants.SOM_ARMY_OUT, rs.getInt("land"), rs
					 .getInt("generals")));
			}
			if (som.getKdloc().equals("(" + Main.getBotSettings().getKdLoc() + ")")) {
			   som.setIsSelfKD(true);
			}
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return som;
   }

   public static String saveSoM(final SoM som) {
	  if (!"".equals(CommonMethods.provinceExists(som.getName()))) {
		 updateProvince(som);
	  } else {
		 addProvince(som);
	  }
	  PreparedStatement ps = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("DELETE FROM som WHERE name = ?");
		 ps.setString(1, som.getName());
		 ps.executeUpdate();

		 int solds = 0;
		 int dspecs = 0;
		 int ospecs = 0;
		 int elites = 0;
		 int dspecstraining = 0;
		 int ospecstraining = 0;
		 int elitestraining = 0;
		 int thievestraining = 0;
		 for (SoMArmy army : som.getArmies()) {
			if (army.getArmyType() == Constants.SOM_ARMY_HOME) {
			   solds = army.getSoldiers();
			   dspecs = army.getDspecs();
			   ospecs = army.getOspecs();
			   elites = army.getElites();
			} else if (army.getArmyType() == Constants.SOM_ARMY_TRAINING) {
			   dspecstraining = army.getDspecs();
			   ospecstraining = army.getOspecs();
			   elitestraining = army.getElites();
			   thievestraining = army.getThieves();
			}
		 }

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO som VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		 ps.setString(1, som.getName());
		 ps.setInt(2, som.getNetDefense());
		 ps.setInt(3, som.getNetOffense());
		 ps.setInt(4, solds);
		 ps.setInt(5, dspecs);
		 ps.setInt(6, dspecstraining);
		 ps.setInt(7, ospecs);
		 ps.setInt(8, ospecstraining);
		 ps.setInt(9, elites);
		 ps.setInt(10, elitestraining);
		 ps.setInt(11, thievestraining);
		 ps.setTimestamp(12, new Timestamp(som.getUpdated().getTime()));
		 ps.setString(13, som.getExportLine());
		 ps.setString(14, som.getSavedBy());
		 ps.executeUpdate();

		 for (SoMArmy army : som.getArmies()) {
			if (army.getArmyType() == Constants.SOM_ARMY_OUT) {
			   try {
				  ps.close();
			   } catch (final Exception e) {
			   }

			   ps = DBConnectionFactory.getConnectionHandler().getConnection()
					 .prepareStatement("INSERT INTO somarmy VALUES (?,?,?,?,?,?,?,?)");
			   ps.setString(1, som.getName());
			   ps.setInt(2, army.getArmyno());
			   ps.setInt(3, army.getSoldiers());
			   ps.setInt(4, army.getOspecs());
			   ps.setInt(5, army.getElites());
			   ps.setTimestamp(6, new Timestamp(army.getReturnTime()));
			   ps.setInt(7, army.getLand());
			   ps.setInt(8, army.getGenerals());
			   ps.executeUpdate();
			   if (!som.getKdloc().equals("(" + Main.getBotSettings().getKdLoc() + ")")) {
				  Bot.getInstance().getTimerHandler().removeArmyTimer("prov " + som.getName(), army.getArmyno());
				  Bot.getInstance()
						.getTimerHandler()
						.addArmyTimer(
							  new ArmyTimer("prov " + som.getName(), army.getArmyno(), army.getReturnTime()
									- Calendar.getInstance().getTimeInMillis()));
			   }
			}
		 }
		 if (!som.getArmies().isEmpty()) {
			out = "SoM added";
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Updates the province with information from the SoM
    * 
    * @param som
    */
   private static void updateProvince(final SoM som) {
	  PreparedStatement ps = null;
	  try {
		 ps = DBConnectionFactory
			   .getConnectionHandler()
			   .getConnection()
			   .prepareStatement(
					 "UPDATE province SET " + "title = ?, kdloc = ?, rulername = ?," + "race = ?, personality = ? WHERE name = ?");
		 ps.setString(1, som.getTitle());
		 ps.setString(2, som.getKdloc());
		 ps.setString(3, som.getRulerName());
		 ps.setString(4, som.getRace());
		 ps.setString(5, som.getPersonality());
		 ps.setString(6, som.getName());
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }
}
